package dao;

//import cn.smbms.dao.BaseDao;
import com.alibaba.fastjson.JSONArray;
import daoBean.*;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;
import org.junit.jupiter.api.Test;
import utils.Customer;
import utils.JDBCUtils2;


/**
 * dao层抛出异常，让service层去捕获处理
 * @author Administrator
 *
 */
public class UserDaoImpl implements UserDao{

	@Override
	// 添加用户操作
	public int addUser(Connection conn, User user) throws Exception {
		// TODO Auto-generated method stub
		int insertCount = 0;
		System.out.println(user.toString());
		try {
			QueryRunner runner = new QueryRunner();
			conn = JDBCUtils2.getConnection3();
			String sql = "insert into smbms_user(userName,phone,address,id,userPassword)values(?,?,?,?,?)"; // sql 语句决定 增删改

			Object[] params = {user.getUserName(),user.getPhone(),user.getAddress(),user.getId(),user.getUserPassword()};
			// runner.update
			insertCount = runner.update(conn, sql, params);
			System.out.println("添加了" + insertCount + "条记录");
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{

			JDBCUtils2.closeResource(conn, null);
		}
		return insertCount;
	}
/*
	@Override
	public User getLoginUser(Connection connection, String userCode)
			throws Exception {
		// TODO Auto-generated method stub
		PreparedStatement pstm = null;
		ResultSet rs = null;
		User user = null;
		if(null != connection){
			String sql = "select * from smbms_user where userCode=?";
			Object[] params = {userCode};
			rs = BaseDao.execute(connection, pstm, rs, sql, params);
			if(rs.next()){
				user = new User();
				user.setId(rs.getInt("id"));
				user.setUserCode(rs.getString("userCode"));
				user.setUserName(rs.getString("userName"));
				user.setUserPassword(rs.getString("userPassword"));
				user.setGender(rs.getInt("gender"));
				user.setBirthday(rs.getDate("birthday"));
				user.setPhone(rs.getString("phone"));
				user.setAddress(rs.getString("address"));
				user.setUserRole(rs.getInt("userRole"));
				user.setCreatedBy(rs.getInt("createdBy"));
				user.setCreationDate(rs.getTimestamp("creationDate"));
				user.setModifyBy(rs.getInt("modifyBy"));
				user.setModifyDate(rs.getTimestamp("modifyDate"));
			}
			BaseDao.closeResource(null, pstm, rs);
		}
		return user;
	}

	@Override
	public List<User> getUserList(Connection connection, String userName, int userRole, int currentPageNo, int pageSize)
			throws Exception {
		// TODO Auto-generated method stub
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<User> userList = new ArrayList<User>();
		if(connection != null){
			StringBuffer sql = new StringBuffer();
			sql.append("select u.*,r.roleName as userRoleName from smbms_user u,smbms_role r where u.userRole = r.id");
			List<Object> list = new ArrayList<Object>();
			if(!StringUtils.isNullOrEmpty(userName)){
				sql.append(" and u.userName like ?");
				list.add("%"+userName+"%");
			}
			if(userRole > 0){
				sql.append(" and u.userRole = ?");
				list.add(userRole);
			}
			sql.append(" order by creationDate DESC limit ?,?");
			currentPageNo = (currentPageNo-1)*pageSize;
			list.add(currentPageNo);
			list.add(pageSize);
			
			Object[] params = list.toArray();
			System.out.println("sql ----> " + sql.toString());
			rs = BaseDao.execute(connection, pstm, rs, sql.toString(), params);
			while(rs.next()){
				User _user = new User();
				_user.setId(rs.getInt("id"));
				_user.setUserCode(rs.getString("userCode"));
				_user.setUserName(rs.getString("userName"));
				_user.setGender(rs.getInt("gender"));
				_user.setBirthday(rs.getDate("birthday"));
				_user.setPhone(rs.getString("phone"));
				_user.setUserRole(rs.getInt("userRole"));
				_user.setUserRoleName(rs.getString("userRoleName"));
				userList.add(_user);
			}
			BaseDao.closeResource(null, pstm, rs);
		}
		return userList;
	}

	@Override
	public int deleteUserById(Connection connection,Integer delId) throws Exception {
		// TODO Auto-generated method stub
		PreparedStatement pstm = null;
		int flag = 0;
		if(null != connection){
			String sql = "delete from smbms_user where id=?";
			Object[] params = {delId};
			flag = BaseDao.execute(connection, pstm, sql, params);
			BaseDao.closeResource(null, pstm, null);
		}
		return flag;
	}
*/

	@Test
	public void getUserById() throws Exception {
		// TODO Auto-generated method stub
	/*	User user = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		if(null != connection){*/
//			String sql = "select u.*,r.roleName as userRoleName from smbms_user u,smbms_role r where u.id=? and u.userRole = r.id";
		/*	Object[] params = {id};
			rs = BaseDao.execute(connection, pstm, rs, sql, params);
			if(rs.next()){
				user = new User();
				user.setId(rs.getInt("id"));
				user.setUserCode(rs.getString("userCode"));
				user.setUserName(rs.getString("userName"));
				user.setUserPassword(rs.getString("userPassword"));
				user.setGender(rs.getInt("gender"));
				user.setBirthday(rs.getDate("birthday"));
				user.setPhone(rs.getString("phone"));
				user.setAddress(rs.getString("address"));
				user.setUserRole(rs.getInt("userRole"));
				user.setCreatedBy(rs.getInt("createdBy"));
				user.setCreationDate(rs.getTimestamp("creationDate"));
				user.setModifyBy(rs.getInt("modifyBy"));
				user.setModifyDate(rs.getTimestamp("modifyDate"));
				user.setUserRoleName(rs.getString("userRoleName"));
			}
			BaseDao.closeResource(null, pstm, rs);*/
//		}
//		return user;

	Connection	conn = null;
	String	id = "1";
		try {
			QueryRunner runner = new QueryRunner();
			conn = JDBCUtils2.getConnection3();
			String sql = "select * from smbms_user where id = ?";
			MapHandler handler = new MapHandler();
			// runner.query
			Map<String, Object> map = runner.query(conn, sql, handler, id);
			System.out.println(map);
			System.out.println(JSONArray.toJSONString(map));
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtils2.closeResource(conn, null);

		}
	}

	@Override
	public User getUserById(Connection conn, String id) throws Exception {
		User user = null;
		try {
			QueryRunner runner = new QueryRunner();
			//conn = JDBCUtils2.getConnection3();
			String sql = "select * from smbms_user where id = ?"; //
			// MapHandler handler = new MapHandler(); // MapHandler把一条sql的记录封装到map中，列名是key，值是value
			// runner.query
			// Map<String, Object> map = runner.query(conn, sql, handler, id);

			BeanHandler<User> handler = new BeanHandler<>(User.class);//BeanHandler
			user = runner.query(conn, sql, handler, 18);
//			System.out.println(user.toString());
//			System.out.println("map:"+map);

			/*if (map!=null){
				 // System.out.println(JSONArray.toJSONString(map)); map = >json
				user.setId((Integer) map.get("id"));
				user.setUserName((String) map.get("userName"));
				String pwd = (String) map.get("userPassword");
				user.setUserPassword(pwd);
				String phone = (String) map.get("phone");
				user.setPhone(phone);
				String address = (String) map.get("address");
				user.setAddress(address);
			}else {
				user = null;
			}*/

		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtils2.closeResource(conn, null);
		}
		// 返回user不能写在finally里面
		return user;
	}
	@Override
	public int modify(Connection conn, User user) throws Exception {
		// TODO Auto-generated method stub
		int insertCount = 0;
		System.out.println(user.toString());
		try {
			QueryRunner runner = new QueryRunner();
//			conn = JDBCUtils2.getConnection3();
			String sql = "update smbms_user set userName=?, phone=?, address=? where id = ? "; // sql 语句决定 增删改
			// runner.update  insertCount>0成功
			Object[] params = {user.getUserName(),user.getPhone(),user.getAddress(),user.getId()};
			insertCount = runner.update(conn, sql, params);

//			System.out.println("添加了" + insertCount + "条记录");
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			JDBCUtils2.closeResource(conn, null);
		}
		return insertCount;
	}
}
/*
	@Override
	public int updatePwd(Connection connection, int id, String pwd)
			throws Exception {
		// TODO Auto-generated method stub
		int flag = 0;
		PreparedStatement pstm = null;
		if(connection != null){
			String sql = "update smbms_user set userPassword= ? where id = ?";
			Object[] params = {pwd,id};
			flag = BaseDao.execute(connection, pstm, sql, params);
			BaseDao.closeResource(null, pstm, null);
		}
		return flag;
	}

	@Override
	public int getUserCount(Connection connection, String userName, int userRole)
			throws Exception {
		// TODO Auto-generated method stub
		PreparedStatement pstm = null;
		ResultSet rs = null;
		int count = 0;
		if(connection != null){
			StringBuffer sql = new StringBuffer();
			sql.append("select count(1) as count from smbms_user u,smbms_role r where u.userRole = r.id");
			List<Object> list = new ArrayList<Object>();
			if(!StringUtils.isNullOrEmpty(userName)){
				sql.append(" and u.userName like ?");
				list.add("%"+userName+"%");
			}
			if(userRole > 0){
				sql.append(" and u.userRole = ?");
				list.add(userRole);
			}
			Object[] params = list.toArray();
			System.out.println("sql ----> " + sql.toString());
			rs = BaseDao.execute(connection, pstm, rs, sql.toString(), params);
			if(rs.next()){
				count = rs.getInt("count");
			}
			BaseDao.closeResource(null, pstm, rs);
		}
		return count;
	}

	*/

